﻿using Newtonsoft.Json;
using QskjBaseDataToDb.Dto;
using QskjLibrary;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QskjBaseDataToDb.Service
{
    public class CarDriverDataUpdate
    {
        /// <summary>
        /// 锐明驾驶员数据
        /// </summary>
        public static void UpdateCarData()
        {
            try
            {
                //string dtStrPre = DateTime.Now.AddDays(-2).ToString("yyyyMMdd");
                //string sqlPre = $@"drop table if exists qskjrecord.B_TaxiDriverRm{dtStrPre}";
                //MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, sqlPre);
                string dtStr = DateTime.Now.ToString("yyyyMMdd");
                if (DateTime.Now.Hour < 7)
                {
                    string exists = $@"SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'qskjrecord' AND TABLE_NAME = 'B_TaxiDriverRm{dtStr}';";
                    object resExi = MySqlHelper.ExecuteScalar(CommandType.Text, exists);
                    if (resExi == null || resExi == DBNull.Value)
                    {
                        string sql = $@"create table IF NOT EXISTS qskjrecord.B_TaxiDriverRm{dtStr}
                        (
                           id                   bigint not null auto_increment,
                           address              varchar(50),
                           alipayVersion        varchar(50),
                           assessmentCompany    varchar(50),
                           assessmentCore       varchar(50),
                           assessmentEndTime    varchar(50),
                           assessmentLv         varchar(50),
                           assessmentRecord     varchar(50),
                           assessmentStartTime  varchar(50),
                           authStrategy         varchar(50),
                           bankCard             varchar(50),
                           bankMerCode          varchar(50),
                           bornDate             varchar(50),
                           carId                varchar(50),
                           carNo                varchar(50),
                           certifiPhoto         varchar(300),
                           certifiPhotoVersion  varchar(50),
                           certificateId        varchar(50),
                           certificateOrganization varchar(50),
                           certificateStatus    varchar(50),
                           certificateType      varchar(50),
                           codepath             varchar(300),
                           companyId            varchar(50),
                           companyName          varchar(50),
                           complaintPhone       varchar(50),
                           contactAddress       varchar(50),
                           creditLevel          varchar(50),
                           datPath              varchar(300),
                           datVersion           varchar(50),
                           driverLicense        varchar(50),
                           driverVersion        varchar(50),
                           email                varchar(50),
                           employmentStatus     varchar(50),
                           grade                varchar(50),
                           homeTel              varchar(50),
                           httpQrcodContent     varchar(50),
                           integrityAssessmentTime varchar(50),
                           isChat               varchar(50),
                           joinGroupDate        varchar(50),
                           latestUpdateTime     varchar(50),
                           major                varchar(50),
                           male                 varchar(50),
                           name                 varchar(50),
                           nation               varchar(50),
                           nationality          varchar(50),
                           password             varchar(50),
                           personalId           varchar(50),
                           phone                varchar(50),
                           photoPath            varchar(300),
                           photoVersion         varchar(50),
                           qrcodPath            varchar(300),
                           recordNumber         varchar(50),
                           registerDate         varchar(50),
                           remark               varchar(50),
                           score                varchar(50),
                           serviceNo            varchar(50),
                           singleShift          varchar(50),
                           superviseDepartment  varchar(50),
                           supervisePhone1      varchar(50),
                           supervisePhone2      varchar(50),
                           validityStartTime    varchar(50),
                           validityTime         varchar(50),
                           violationScore       varchar(50),
                           wechatVersion        varchar(50),
                           wildCard             varchar(50),
                           workTime             varchar(50),
                           wxCodePath           varchar(300),
                           yzfCodePath          varchar(300),
                           yzfVersion           varchar(50),
                           zipcode              varchar(50),
                           primary key (id)
                        );";
                        MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, sql);
                    }
                }
                string sqlCount = $@"select count(*) from qskjrecord.B_TaxiDriverRm{dtStr}";
                if (Convert.ToInt32(MySqlHelper.ExecuteScalar(CommandType.Text, sqlCount)) > 0)
                {
                    Console.WriteLine(DateTime.Now + "读取锐明出租车 驾驶员信息  数据已读取成功");
                    return;
                }
                //读取数据
                var str = HttpHelper.GetDriver().Result;
                List<TaxiDriverInfoDto> obj = JsonConvert.DeserializeObject<List<TaxiDriverInfoDto>>(str);
                foreach (var item in obj)
                {
                    string insertSql = $@"insert into qskjrecord.B_TaxiDriverRm{dtStr}(address,alipayVersion,assessmentCompany,assessmentCore,assessmentEndTime,assessmentLv,assessmentRecord,assessmentStartTime,
                                        authStrategy,bankCard,bankMerCode,bornDate,carId,carNo,certifiPhoto,certifiPhotoVersion,certificateId,certificateOrganization,certificateStatus,certificateType,
                                        codepath,companyId,companyName,complaintPhone,contactAddress,creditLevel,datPath,datVersion,driverLicense,driverVersion,email,employmentStatus,grade,homeTel,
                                        httpQrcodContent,integrityAssessmentTime,isChat,joinGroupDate,latestUpdateTime,major,male,name,nation,nationality,password,personalId,phone,photoPath,
                                        photoVersion,qrcodPath,recordNumber,registerDate,remark,score,serviceNo,singleShift,superviseDepartment,supervisePhone1,supervisePhone2,validityStartTime,
                                        validityTime,violationScore,wechatVersion,wildCard,workTime,wxCodePath,yzfCodePath,yzfVersion,zipcode)
                                        values('{item.address}','{item.alipayVersion}','{item.assessmentCompany}','{item.assessmentCore}','{item.assessmentEndTime}','{item.assessmentLv}','{item.assessmentRecord}','{item.assessmentStartTime}',
                                                '{item.authStrategy}','{item.bankCard}','{item.bankMerCode}','{item.bornDate}','{item.carId}','{item.carNo}','{item.certifiPhoto}','{item.certifiPhotoVersion}','{item.certificateId}','{item.certificateOrganization}','{item.certificateStatus}','{item.certificateType}',
                                                '{item.codepath}','{item.companyId}','{item.companyName}','{item.complaintPhone}','{item.contactAddress}','{item.creditLevel}','{item.datPath}','{item.datVersion}','{item.driverLicense}','{item.driverVersion}','{item.email}','{item.employmentStatus}','{item.grade}','{item.homeTel}',
                                                '{item.httpQrcodContent}','{item.integrityAssessmentTime}','{item.isChat}','{item.joinGroupDate}','{item.latestUpdateTime}','{item.major}','{item.male}','{item.name}','{item.nation}','{item.nationality}','{item.password}','{item.personalId}','{item.phone}','{item.photoPath}',
                                                '{item.photoVersion}','{item.qrcodPath}','{item.recordNumber}','{item.registerDate}','{item.remark}','{item.score}','{item.serviceNo}','{item.singleShift}','{item.superviseDepartment}','{item.supervisePhone1}','{item.supervisePhone2}','{item.validityStartTime}',
                                                '{item.validityTime}','{item.violationScore}','{item.wechatVersion}','{item.wildCard}','{item.workTime}','{item.wxCodePath}','{item.yzfCodePath}','{item.yzfVersion}','{item.zipcode}')";
                    MySqlHelper.ExecuteNonQuery(System.Data.CommandType.Text, insertSql);
                }

            }
            catch (Exception e)
            {
                Console.WriteLine("读取锐明出租车 驾驶员信息异常" + JsonConvert.SerializeObject(e));
            }
        }
    }
}
